package com.itic.appbase.applications.exports.ExportExcelWithZip;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.DirectoryEntry;
import org.apache.poi.poifs.filesystem.DocumentEntry;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * <p>Title: Poi工具类</p>
 * <p>Description: 中科软资料库</p>
 * <p>Copyright: Copyright (c) 2010-2013</p>
 * <p>Company: 中科软</p>
 * <p>@author 高晨晨</p>
 * <p>© 2004 sinosoft.com.cn</p>
 */
public class PoiUtils{
	
	/**
	 * 导出Excel表格到本地
	 * 
	 * @param virtualTitle
	 * 						虚拟标题
	 * @param realTitle
	 * 						Excel显示的标题
	 * @param objectList
	 * 						对象列表
	 */
	public static void exportExcel(String[] virtualTitle, String[] realTitle,
			List<Object> objectList) throws Exception {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("sheet1");
		// 创建字体
		HSSFFont font = workbook.createFont();
		font.setColor(Font.COLOR_RED);
		font.setItalic(true);
		font.setFontHeightInPoints((short) 13);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 创建单元格样式
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setFont(font);
		// 居中显示
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		int k = 0;
		HSSFRow row = sheet.createRow(k);
		// 添加标题
		for (int i = 0; i < realTitle.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(realTitle[i]);
		}
		// 设置标题行的宽高
		for (int i = 0; i < realTitle.length; i++) {
			sheet.setColumnWidth(i, realTitle[i].getBytes().length * 256 * 3);
		}

		// 添加数据
		HSSFCellStyle cellStyleCenter = workbook.createCellStyle();
		cellStyleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		k++;
		for (Object object : objectList) {
			row = sheet.createRow(k);
			// 利用反射机制来向Excel中填充数据
			Method[] methods = object.getClass().getMethods();
			for (Method method : methods) {
				for (int i = 0; i < virtualTitle.length; i++) {
					String newTitle = toUpper(virtualTitle[i]);
					String methodName = "get" + newTitle;
					if (method.getName().equals(methodName)) {
						HSSFCell newCell = row.createCell(i);
						newCell.setCellStyle(cellStyleCenter);
						newCell.setCellValue("" + method.invoke(object));
					}
				}
			}
			k++;
		}
		// 随机生成文件名
		Random random = new Random();
		String fileName = "file" + random.nextInt() + ".xls";
		// 获取本地的桌面路径，默认将导出的Excel文件存放在桌面上
		javax.swing.filechooser.FileSystemView fsv = javax.swing.filechooser.FileSystemView
				.getFileSystemView();
		String filePath = fsv.getHomeDirectory().toString();
		FileOutputStream out = new FileOutputStream(filePath + "\\" + fileName);
		workbook.write(out);
	}

	/**
	 * 将字符串的第一个字母变成大写
	 */
	public static String toUpper(String str) {
		byte[] items = str.getBytes();
		items[0] = (byte) ((char) items[0] - 'a' + 'A');
		return new String(items);
	}
	
	/**
	 * 导出Excel表格到客户端
	 * 
	 * @param virtualTitle
	 * 						虚拟标题
	 * @param realTitle
	 * 						Excel表格的标题
	 * @param objectList
	 * 						对象列表
	 * @param os
	 * 						客户端的输入流
	 */
	public static void exportExcelToClient(String[] virtualTitle, String[] realTitle,
			List<Object> objectList,OutputStream os) throws Exception {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("sheet1");
		// 创建字体
		HSSFFont font = workbook.createFont();
		// 设置字体格式为红色
		//font.setColor(Font.COLOR_RED);
		// 设置字体格式为斜体
		//font.setItalic(true);
		font.setFontHeightInPoints((short) 13);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 创建单元格样式
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setFont(font);
		// 居中显示
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		int k = 0;
		HSSFRow row = sheet.createRow(k);
		// 添加标题
		for (int i = 0; i < realTitle.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(realTitle[i]);
		}
		// 设置标题行的宽高
		for (int i = 0; i < realTitle.length; i++) {
			sheet.setColumnWidth(i, realTitle[i].getBytes().length * 256 * 3);
		}

		// 添加数据
		HSSFCellStyle cellStyleCenter = workbook.createCellStyle();
		cellStyleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		k++;
		for (Object object : objectList) {
			row = sheet.createRow(k);
			// 利用反射机制来向Excel中填充数据
			Method[] methods = object.getClass().getMethods();
			for (Method method : methods) {
				for (int i = 0; i < virtualTitle.length; i++) {
					String newTitle = toUpper(virtualTitle[i]);
					String methodName = "get" + newTitle;
					if (method.getName().equals(methodName)) {
						HSSFCell newCell = row.createCell(i);
						newCell.setCellStyle(cellStyleCenter);
						newCell.setCellValue("" + method.invoke(object));
					}
				}
			}
			k++;
		}
		os.flush();
		workbook.write(os);
	}
	
	/**
	 * 根据模板文件导出Excel表格到客户端(设置起始行)
	 * 
	 * @param virtualTitle
	 * 						虚拟标题
	 * @param templetPath
	 * 						Excel模板路径
	 * @param startRow
	 * 						写入数据起始行
	 * @param objectList
	 * 						对象列表
	 * @param os
	 * 						客户端的输入流
	 */
	public static void exportExcelToClientByTemplet(String[] virtualTitle, String templetPath, int startRow, List<Object> objectList,OutputStream os) throws Exception {
		// 读取模板文件
		FileInputStream in = new FileInputStream(new File(templetPath)); 
		HSSFWorkbook workbook = new HSSFWorkbook(in);
		// 获取第一个工作簿
		HSSFSheet sheet = workbook.getSheetAt(0);
		int k = startRow;
		HSSFRow row = null;
		// 添加数据
		HSSFCellStyle cellStyleCenter = workbook.createCellStyle();
		cellStyleCenter.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		for (Object object : objectList) {
			row = sheet.createRow(k);
			// 利用反射机制来向Excel中填充数据
			Method[] methods = object.getClass().getMethods();
			for (Method method : methods) {
				for (int i = 0; i < virtualTitle.length; i++) {
					String newTitle = toUpper(virtualTitle[i]);
					String methodName = "get" + newTitle;
					if (method.getName().equals(methodName)) {
						HSSFCell newCell = row.createCell(i);
						newCell.setCellStyle(cellStyleCenter);
						newCell.setCellValue("" + method.invoke(object));
					}
				}
			}
			k++;
		}
		os.flush();
		workbook.write(os);
	}
	
	public static void exportExcelToClientByTemplet(String[] virtualTitle, String templetPath, int startRow, int startColumn, List<?> objectList,OutputStream os) throws Exception {
		// 读取模板文件
		FileInputStream in = new FileInputStream(new File(templetPath)); 
		HSSFWorkbook workbook = new HSSFWorkbook(in);
		HSSFCellStyle cellStyleCenter = workbook.createCellStyle();
		// 居中显示
		cellStyleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyleCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 加边框
		cellStyleCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setBottomBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setLeftBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setRightBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setTopBorderColor(HSSFColor.BLACK.index);
		int count = (int)Math.ceil((double)objectList.size()/60000);
		for(int index = 1 ; index <= count ; index++) {
			// 获取第index个工作簿
			HSSFSheet sheet = workbook.getSheetAt(index-1);
			int k = startRow;
			HSSFRow row = null;
			// 添加数据
			for (int sequence = (index-1)*60000 ; index != count ? sequence <= (index*60000 - 1) : sequence < objectList.size() ; sequence++) {
				Object object = objectList.get(sequence);
				row = sheet.createRow(k);
				// 利用反射机制来向Excel中填充数据
				//Method[] methods = object.getClass().getMethods();
				Method[] methods = object.getClass().getDeclaredMethods();
				for (Method method : methods) {
					int j = 0;
					for (int i = startColumn; i < virtualTitle.length + startColumn; i++) {
						String newTitle = toUpper(virtualTitle[j]);
						String methodName = "get" + newTitle;
						if (method.getName().equals(methodName)) {
							HSSFCell newCell = row.createCell(i);
							newCell.setCellStyle(cellStyleCenter);
							newCell.setCellValue("" + method.invoke(object));
						}
						j++;
					}
				}
				k++;
			}
		}
		os.flush();
		workbook.write(os);
	}
	
	
	public static void exportExcelToClientByTemplet(String[] virtualTitle, String templetPath, int startRow, int startColumn, List<?> objectList,OutputStream os, boolean hasParent) throws Exception {
		// 读取模板文件
		FileInputStream in = new FileInputStream(new File(templetPath)); 
		HSSFWorkbook workbook = new HSSFWorkbook(in);
		HSSFCellStyle cellStyleCenter = workbook.createCellStyle();
		// 居中显示
		cellStyleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyleCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 加边框
		cellStyleCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setBottomBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setLeftBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setRightBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setTopBorderColor(HSSFColor.BLACK.index);
		int count = (int)Math.ceil((double)objectList.size()/60000);
		for(int index = 1 ; index <= count ; index++) {
			// 获取第index个工作簿
			HSSFSheet sheet = workbook.getSheetAt(index-1);
			int k = startRow;
			HSSFRow row = null;
			// 添加数据
			for (int sequence = (index-1)*60000 ; index != count ? sequence <= (index*60000 - 1) : sequence < objectList.size() ; sequence++) {
				Object object = objectList.get(sequence);
				row = sheet.createRow(k);
				// 利用反射机制来向Excel中填充数据
				Method[] methods = object.getClass().getDeclaredMethods();
				if(hasParent){
				    methods = object.getClass().getMethods();
				}
				for (Method method : methods) {
					int j = 0;
					for (int i = startColumn; i < virtualTitle.length + startColumn; i++) {
						String newTitle = toUpper(virtualTitle[j]);
						String methodName = "get" + newTitle;
						if (method.getName().equals(methodName)) {
							HSSFCell newCell = row.createCell(i);
							newCell.setCellStyle(cellStyleCenter);
							newCell.setCellValue("" + method.invoke(object));
						}
						j++;
					}
				}
				k++;
			}
		}
		os.flush();
		workbook.write(os);
	}
	
	
	/**
	 *	将富文本大字段写入到word中
	 */
	public static boolean writeWord(String filepath,String content,String fileName) throws Exception {
		String newFileName = new String(fileName.getBytes("UTF-8"),"ISO8859-1");
		//String newFileName = new String(fileName.getBytes(), "UTF-8");
		//String newFileName = new String((fileName).getBytes("GBK"), System.getProperty("sun.jnu.encoding"));
		boolean flag = false;
		ByteArrayInputStream bais = null;
		FileOutputStream fos = null;
		try {
			if (!"".equals(filepath)) {
				File fileDir = new File(filepath);
				if (fileDir.exists()) {
					byte b[] = content.getBytes("GBK");
					bais = new ByteArrayInputStream(b);
					POIFSFileSystem poifs = new POIFSFileSystem();
					DirectoryEntry directory = poifs.getRoot();
					DocumentEntry documentEntry = directory.createDocument(
							"WordDocument", bais);
					
					fos = new FileOutputStream(filepath + newFileName);
					//fos = new BufferedWriter(,)
					poifs.writeFilesystem(fos);
					bais.close();
					fos.close();
				}
			}

		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (fos != null)
				fos.close();
			if (bais != null)
				bais.close();
		}
		return flag;
	}
	
	/**
	 * 总库存查询统计报表导出
	 */
	public static void exportExcelByTempletJzwz(String templetPath, int start, String[] virtualTitle,
			List<Object> objectList, OutputStream os) throws Exception {
	// 读取模板
 	FileInputStream in = new FileInputStream(new File(templetPath)); 
	HSSFWorkbook workbook = new HSSFWorkbook(in);
	// 获取第一个工作簿
	HSSFSheet sheet = workbook.getSheetAt(0);
	
	// 创建字体
	HSSFFont font = workbook.createFont();
	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	// 创建单元格样式
	HSSFCellStyle cellStyle = workbook.createCellStyle();
	cellStyle.setFont(font);
	// 居中显示
	cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	// 加边框
	cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
	cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); 
	cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
	cellStyle.setLeftBorderColor(HSSFColor.BLACK.index); 
	cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); 
	cellStyle.setRightBorderColor(HSSFColor.BLACK.index); 
	cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); 
	cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
	
	int k = start;
	HSSFRow row = null;
	// 添加数据
	HSSFCellStyle cellStyleCenter = workbook.createCellStyle();
	cellStyleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	cellStyleCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	cellStyleCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
	cellStyleCenter.setBottomBorderColor(HSSFColor.BLACK.index); 
	cellStyleCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
	cellStyleCenter.setLeftBorderColor(HSSFColor.BLACK.index); 
	cellStyleCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); 
	cellStyleCenter.setRightBorderColor(HSSFColor.BLACK.index); 
	cellStyleCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); 
	cellStyleCenter.setTopBorderColor(HSSFColor.BLACK.index);
	HSSFFont font1 = workbook.createFont();
	cellStyleCenter.setFont(font1);
	for (Object object : objectList) {
		row = sheet.createRow(k);
		row.setHeight((short)530);
		// 利用反射机制来向Excel中填充数据
		Method[] methods = object.getClass().getMethods();
		Method methodGetDqmc = object.getClass().getMethod("getDqmc");
		for (Method method : methods) {
			int j = 0;
			for (int i = 0; i < virtualTitle.length; i++) {
				String newTitle = toUpper(virtualTitle[j]);
				String methodName = "get" + newTitle;
				if (method.getName().equals(methodName)) {
					if(k == start || methodGetDqmc.invoke(object).equals("小计")) {
						HSSFCell newCell = row.createCell(i);
						newCell.setCellStyle(cellStyle);
						if(method.invoke(object) == null || ("" + method.invoke(object)).equals("") || ("" + method.invoke(object)).equals("0")) {
							newCell.setCellValue(0);
						} else {
							if(i == 0) {
								newCell.setCellValue("" + method.invoke(object));
							} else {
								newCell.setCellValue(Double.parseDouble("" + method.invoke(object)));
							}
						}
						
					} else {
						HSSFCell newCell = row.createCell(i);
						newCell.setCellStyle(cellStyleCenter);
						if(method.invoke(object) == null || ("" + method.invoke(object)).equals("") || ("" + method.invoke(object)).equals("0")) {
							newCell.setCellValue(0);
						} else {
							if(i == 0) {
								newCell.setCellValue("" + method.invoke(object));
							} else {
								newCell.setCellValue(Double.parseDouble("" + method.invoke(object)));
							}
						}
					}
					
				}
				j++;
			}
		}
		k++;
	}
	os.flush();
	workbook.write(os);
	}
	
	public static void exportExcelCrk(String[] virtualTitle, String templetPath, int startRow, int startColumn, List<Object> objectList,OutputStream os) throws Exception {
		// 读取模板文件
		FileInputStream in = new FileInputStream(new File(templetPath)); 
		HSSFWorkbook workbook = new HSSFWorkbook(in);
		// 获取第一个工作簿
		HSSFSheet sheet = workbook.getSheetAt(0);
		int k = startRow;
		HSSFRow row = null;
		// 添加数据
		HSSFCellStyle cellStyleCenter = workbook.createCellStyle();
		cellStyleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyleCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setBottomBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setLeftBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setRightBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setTopBorderColor(HSSFColor.BLACK.index);
		for (Object object : objectList) {
			row = sheet.createRow(k);
			// 利用反射机制来向Excel中填充数据
			Method[] methods = object.getClass().getMethods();
			for (Method method : methods) {
				int j = 0;
				for (int i = startColumn; i < virtualTitle.length + startColumn; i++) {
					String newTitle = toUpper(virtualTitle[j]);
					String methodName = "get" + newTitle;
					if (method.getName().equals(methodName)) {
						HSSFCell newCell = row.createCell(i);
						newCell.setCellStyle(cellStyleCenter);
						if(i >= 2) {
							newCell.setCellValue(Double.parseDouble("" + method.invoke(object)));
						} else {
							newCell.setCellValue("" + method.invoke(object));
						}
					}
					j++;
				}
			}
			k++;
		}
		os.flush();
		workbook.write(os);
	}
	
	public static void exportExcelZqxx(String[] virtualTitle, String templetPath, int startRow, int startColumn, List<Object> objectList,OutputStream os) throws Exception {
		// 读取模板文件
		FileInputStream in = new FileInputStream(new File(templetPath)); 
		HSSFWorkbook workbook = new HSSFWorkbook(in);
		// 获取第一个工作簿
		HSSFSheet sheet = workbook.getSheetAt(0);
		int k = startRow;
		HSSFRow row = null;
		// 创建字体(黑体)
		HSSFFont font = workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints((short)11);
		// 创建单元格样式
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setFont(font);
		// 居中显示
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); 
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setLeftBorderColor(HSSFColor.BLACK.index); 
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setRightBorderColor(HSSFColor.BLACK.index); 
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
		
		
		// 合并单元格创建字体
		HSSFFont fontMerger = workbook.createFont();
		fontMerger.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		fontMerger.setFontHeightInPoints((short)20);
		
		// 合并单元格的样式
		HSSFCellStyle cellMergerStyle = workbook.createCellStyle();
		cellMergerStyle.setFont(fontMerger);
		// 居中显示
		cellMergerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellMergerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellMergerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
		cellMergerStyle.setBottomBorderColor(HSSFColor.BLACK.index); 
		cellMergerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
		cellMergerStyle.setLeftBorderColor(HSSFColor.BLACK.index); 
		cellMergerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); 
		cellMergerStyle.setRightBorderColor(HSSFColor.BLACK.index); 
		cellMergerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); 
		cellMergerStyle.setTopBorderColor(HSSFColor.BLACK.index);
		
		HSSFFont fontBlack = workbook.createFont();
		fontBlack.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		
		// 添加数据
		HSSFCellStyle cellStyleCenter = workbook.createCellStyle();
		cellStyleCenter.setFont(fontBlack);
		cellStyleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyleCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setBottomBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setLeftBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setRightBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setTopBorderColor(HSSFColor.BLACK.index);
		
		HSSFCellStyle cellStyleCenter1 = workbook.createCellStyle();
		cellStyleCenter1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyleCenter1.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter1.setBottomBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter1.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter1.setLeftBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter1.setBorderRight(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter1.setRightBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter1.setBorderTop(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter1.setTopBorderColor(HSSFColor.BLACK.index);
		for (Object object : objectList) {
			row = sheet.createRow(k);
			// 利用反射机制来向Excel中填充数据
			Method[] methods = object.getClass().getMethods();
			Method methodZjjjss = object.getClass().getMethod("getZjjjss");
			Method methodZhzl = object.getClass().getMethod("getDw");
			if(methodZjjjss.invoke(object) != null) {
				if((("" + methodZhzl.invoke(object)).contains("州") 
						|| ("" + methodZhzl.invoke(object)).contains("市") 
						|| ("" + methodZhzl.invoke(object)).contains("小计")
						|| ("" + methodZhzl.invoke(object)).contains("全省合计"))
						&& (!("" + methodZhzl.invoke(object)).contains("市辖区"))
						&& (!("" + methodZhzl.invoke(object)).contains("格尔木市"))
						&& (!("" + methodZhzl.invoke(object)).contains("德令哈市"))) {
					for (Method method : methods) {
						int j = 0;
						for (int i = startColumn; i < virtualTitle.length + startColumn; i++) {
							String newTitle = toUpper(virtualTitle[j]);
							String methodName = "get" + newTitle;
							if (method.getName().equals(methodName)) {
								if(i == startColumn) {
									HSSFCell newCell = row.createCell(i);
									newCell.setCellStyle(cellStyle);
									newCell.setCellValue("" + method.invoke(object));
								} else {
									HSSFCell newCell = row.createCell(i);
									newCell.setCellStyle(cellStyleCenter);
									if(method.invoke(object) == null) {
										newCell.setCellValue(Double.parseDouble("0"));
									} else {
										newCell.setCellValue(Double.parseDouble("" + method.invoke(object)));
									}
								}
							}
							j++;
						}
					}
				} else {
					for (Method method : methods) {
						int j = 0;
						for (int i = startColumn; i < virtualTitle.length + startColumn; i++) {
							String newTitle = toUpper(virtualTitle[j]);
							String methodName = "get" + newTitle;
							if (method.getName().equals(methodName)) {
								if(i == startColumn) {
									HSSFCell newCell = row.createCell(i);
									newCell.setCellStyle(cellStyleCenter1);
									newCell.setCellValue("" + method.invoke(object));
								} else {
									HSSFCell newCell = row.createCell(i);
									newCell.setCellStyle(cellStyleCenter1);
									if(method.invoke(object) == null) {
										newCell.setCellValue(Double.parseDouble("0"));
									} else {
										newCell.setCellValue(Double.parseDouble("" + method.invoke(object)));
									}
								}
							}
							j++;
						}
					}
				}
			} else {
				HSSFCell newCell = row.createCell(0);
				newCell.setCellStyle(cellMergerStyle);
				newCell.setCellValue("" + methodZhzl.invoke(object));
				sheet.addMergedRegion(new CellRangeAddress(k,k,0,17));
			}
			k++;
		}
		os.flush();
		workbook.write(os);
	}
	
	/**
	 * 全省民政灾害信息员救灾设备分配表
	 */
	public static void exportExcelJzsbfpb(String[] virtualTitle, String templetPath, int startRow, int startColumn, List<?> objectList,OutputStream os) throws Exception {
		// 读取模板文件
		FileInputStream in = new FileInputStream(new File(templetPath)); 
		HSSFWorkbook workbook = new HSSFWorkbook(in);
		// 创建字体(黑体)
		HSSFFont font = workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints((short)11);
		// 创建单元格样式
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setFont(font);
		// 居中显示
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); 
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setLeftBorderColor(HSSFColor.BLACK.index); 
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setRightBorderColor(HSSFColor.BLACK.index); 
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
		
		HSSFCellStyle cellStyleCenter = workbook.createCellStyle();
		cellStyleCenter.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		cellStyleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyleCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyleCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setBottomBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setLeftBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setRightBorderColor(HSSFColor.BLACK.index); 
		cellStyleCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); 
		cellStyleCenter.setTopBorderColor(HSSFColor.BLACK.index);
		int count = (int)Math.ceil((double)objectList.size()/60000);
		for(int index = 1 ; index <= count ; index++) {
			// 获取第index个工作簿
			HSSFSheet sheet = workbook.getSheetAt(index-1);
			int k = startRow;
			HSSFRow row = null;
			// 添加数据
			for (int sequence = (index-1)*60000 ; index != count ? sequence <= (index*60000 - 1) : sequence < objectList.size() ; sequence++) {
				Object object = objectList.get(sequence);
				Method methodGetXzqhdm = object.getClass().getMethod("getXzjgmc");
				row = sheet.createRow(k);
				// 利用反射机制来向Excel中填充数据
				Method[] methods = object.getClass().getMethods();
				if(("" + methodGetXzqhdm.invoke(object)).contains("全省总计")
						|| ("" + methodGetXzqhdm.invoke(object)).contains("省民政厅")
						|| ("" + methodGetXzqhdm.invoke(object)).contains("合计")) {
					for (Method method : methods) {
						int j = 0;
						for (int i = startColumn; i < virtualTitle.length + startColumn; i++) {
							String newTitle = toUpper(virtualTitle[j]);
							String methodName = "get" + newTitle;
							if (method.getName().equals(methodName)) {
								HSSFCell newCell = row.createCell(i);
								newCell.setCellStyle(cellStyle);
								newCell.setCellValue(method.invoke(object) != null ? "" + method.invoke(object) : "");
							}
							j++;
						}
					}
					
				} else {
					for (Method method : methods) {
						int j = 0;
						for (int i = startColumn; i < virtualTitle.length + startColumn; i++) {
							String newTitle = toUpper(virtualTitle[j]);
							String methodName = "get" + newTitle;
							if (method.getName().equals(methodName)) {
								HSSFCell newCell = row.createCell(i);
								newCell.setCellStyle(cellStyleCenter);
								newCell.setCellValue(method.invoke(object) != null ? "" + method.invoke(object) : "");
							}
							j++;
						}
					}
				}
				k++;
			}
		}
		os.flush();
		workbook.write(os);
	}
	
	public static void exportExcelByDsc(String templetPath, int startRow, int startColumn, List<?> objList, Map<String, Object> map, OutputStream os) throws Exception {
		// 读取模板文件
		FileInputStream in = new FileInputStream(new File(templetPath)); 
		HSSFWorkbook workbook = new HSSFWorkbook(in);
		// 获取第一个工作簿
		HSSFSheet sheet = workbook.getSheetAt(0);
		int k = startRow;
		HSSFRow row = null;
		// 标题头的样式
		HSSFCellStyle cellMergerStyle = workbook.createCellStyle();
		cellMergerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellMergerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellMergerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
		cellMergerStyle.setBottomBorderColor(HSSFColor.BLACK.index); 
		cellMergerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
		cellMergerStyle.setLeftBorderColor(HSSFColor.BLACK.index); 
		cellMergerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); 
		cellMergerStyle.setRightBorderColor(HSSFColor.BLACK.index); 
		cellMergerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); 
		cellMergerStyle.setTopBorderColor(HSSFColor.BLACK.index);
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 13);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		cellMergerStyle.setFont(font);
		
		// 数据的样式
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); 
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setLeftBorderColor(HSSFColor.BLACK.index); 
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setRightBorderColor(HSSFColor.BLACK.index); 
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
		
		// 添加数据(head)
		row = sheet.createRow(k);
		HSSFCell newCell = row.createCell(0);
		newCell.setCellStyle(cellMergerStyle);
		newCell.setCellValue("指标");
		sheet.addMergedRegion(new CellRangeAddress(0,1,0,0));
		
		// 获取年份
		List<String> years = new ArrayList<String>();
		Set<String> keys = map.keySet();
		Iterator<String> iter = keys.iterator();
		while(iter.hasNext()) {
			years.add(iter.next());
		}
		
		int flag = 1;
		for(int i = 0 ; i < years.size() ; i++) {
			HSSFCell newCell1 = row.createCell(flag);
			newCell1.setCellStyle(cellMergerStyle);
			newCell1.setCellValue(years.get(i));
			sheet.addMergedRegion(new CellRangeAddress(0,0,flag,flag+1));
			flag += 2;
		}
		
		k++;
		flag = 1 ;
		row = sheet.createRow(k);
		for(int i = 0 ; i < years.size() ; i++) {
			HSSFCell newCel3 = row.createCell(flag);
			newCel3.setCellStyle(cellMergerStyle);
			newCel3.setCellValue("单位数");
			
			HSSFCell newCel4 = row.createCell(flag+1);
			newCel4.setCellStyle(cellMergerStyle);
			newCel4.setCellValue("总产值");
			flag += 2;
		}
		
		k++;
		flag = 1;
		// 添加数据(body)
		for(int i = 0 ; i < objList.size() ; i++) {
			Object obj = objList.get(i);
			row = sheet.createRow(k);
			// 向第一列添加数据
			//Method method = obj.getClass().getMethod("getZbdm");
			HSSFCell newCell5 = row.createCell(0);
			newCell5.setCellStyle(cellStyle);
			newCell5.setCellValue("" + obj);// 取obj的值
			for(int j = 0 ; j < years.size() ; j++) {
				// 获取每一年的列表
				List<Object> objs = (List<Object>) map.get(years.get(j));
				// 获取第i个对象
				Object object = objs.get(i);
				
				Method methodDwzs = object.getClass().getMethod("getDwgs");
				Method methodZcz = object.getClass().getMethod("getZcz");
				
				HSSFCell newCel6 = row.createCell(flag);
				newCel6.setCellStyle(cellStyle);
				newCel6.setCellValue("" + methodDwzs.invoke(object));// object的值
				
				HSSFCell newCel7 = row.createCell(flag+1);
				newCel7.setCellStyle(cellStyle);
				newCel7.setCellValue("" + methodZcz.invoke(object));// object的值
				flag += 2;
			}
			k++;
			flag = 1;
		}
		os.flush();
		workbook.write(os);
	}
	
	public static void exportExcelByGmjj(String templetPath, int startRow, int startColumn, List<?> objList, Map<String, Object> map1, Map<String, Object> map2, Map<String, Object> map3, OutputStream os) throws Exception {
		// 读取模板文件
		FileInputStream in = new FileInputStream(new File(templetPath)); 
		HSSFWorkbook workbook = new HSSFWorkbook(in);
		// 获取第一个工作簿
		HSSFSheet sheet = workbook.getSheetAt(0);
		int k = startRow;
		HSSFRow row = null;
		// 标题头的样式
		HSSFCellStyle cellMergerStyle = workbook.createCellStyle();
		cellMergerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellMergerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellMergerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
		cellMergerStyle.setBottomBorderColor(HSSFColor.BLACK.index); 
		cellMergerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
		cellMergerStyle.setLeftBorderColor(HSSFColor.BLACK.index); 
		cellMergerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); 
		cellMergerStyle.setRightBorderColor(HSSFColor.BLACK.index); 
		cellMergerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); 
		cellMergerStyle.setTopBorderColor(HSSFColor.BLACK.index);
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 13);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		cellMergerStyle.setFont(font);
		
		// 数据的样式
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); 
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setLeftBorderColor(HSSFColor.BLACK.index); 
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setRightBorderColor(HSSFColor.BLACK.index); 
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
		
		// 添加数据(head)
		row = sheet.createRow(k);
		HSSFCell newCell = row.createCell(0);
		newCell.setCellValue("指标");
		sheet.addMergedRegion(new CellRangeAddress(0,2,0,0));
		newCell.setCellStyle(cellMergerStyle);
		
		// 获取总量指标年份
		List<String> years1 = new ArrayList<String>();
		Set<String> keys1 = map1.keySet();
		Iterator<String> iter1 = keys1.iterator();
		while(iter1.hasNext()) {
			years1.add(iter1.next());
		}
		
		// 获取指数年份
		List<String> years2 = new ArrayList<String>();
		Set<String> keys2 = map2.keySet();
		Iterator<String> iter2 = keys2.iterator();
		while(iter2.hasNext()) {
			years2.add(iter2.next());
		}
		
		// 获取平均增长速度年份
		List<String> years3 = new ArrayList<String>();
		Set<String> keys3 = map3.keySet();
		Iterator<String> iter3 = keys3.iterator();
		while(iter3.hasNext()) {
			years3.add(iter3.next());
		}
		
		// 根据问题指标年份的大小进行合并
		HSSFCell newCell2 = row.createCell(1);
		newCell2.setCellValue("总量指标");
		sheet.addMergedRegion(new CellRangeAddress(0,1,1,years1.size()));
		newCell2.setCellStyle(cellMergerStyle);
		/*合并之后的边框*/
		for(int i = 2 ; i < years1.size() ; i++) {
			HSSFCell newCellEding = row.createCell(i);
			newCellEding.setCellValue("");
			newCellEding.setCellStyle(cellMergerStyle);
		}
		
		int flag = 1;
		k += 2;
		row = sheet.createRow(k);
		for(int i = 0 ; i < years1.size() ; i++) {
			HSSFCell newCel3 = row.createCell(flag);
			newCel3.setCellStyle(cellMergerStyle);
			newCel3.setCellValue(years1.get(i));
			flag++;
		}
		k = 0;
		row = sheet.getRow(k);
		HSSFCell newCell4 = row.createCell(flag);
		newCell4.setCellValue("速度指标");
		sheet.addMergedRegion(new CellRangeAddress(0,0,flag,flag + years2.size() + years3.size()-1));
		newCell4.setCellStyle(cellMergerStyle);
		/*合并之后的边框*/
		for(int i = (flag+1) ; i < (flag + years2.size() + years3.size()) ; i++) {
			HSSFCell newCellEding = row.createCell(i);
			newCellEding.setCellValue("");
			newCellEding.setCellStyle(cellMergerStyle);
		}
		
		k = 1;
		row = sheet.createRow(k);
		HSSFCell newCell5 = row.createCell(flag);
		newCell5.setCellValue("指数");
		sheet.addMergedRegion(new CellRangeAddress(1,1,flag,flag + years2.size()-1));
		newCell5.setCellStyle(cellMergerStyle);
		row.getCell(flag).setCellStyle(cellMergerStyle);
		/*合并之后的边框*/
		for(int i = (flag+1) ; i < (flag + years2.size()-1) ; i++) {
			HSSFCell newCellEding = row.createCell(i);
			newCellEding.setCellValue("");
			newCellEding.setCellStyle(cellMergerStyle);
		}
		
		HSSFCell newCell6 = row.createCell(flag+years2.size());
		newCell6.setCellValue("平均增长速度");
		sheet.addMergedRegion(new CellRangeAddress(1,1,flag+years2.size(),flag + years2.size() + years3.size()-1));
		newCell6.setCellStyle(cellMergerStyle);
		row.getCell(flag+years2.size()).setCellStyle(cellMergerStyle);
		/*合并之后的边框*/
		for(int i = (flag+years2.size()+1) ; i < (flag + years2.size() + years3.size()) ; i++) {
			HSSFCell newCellEding = row.createCell(i);
			newCellEding.setCellValue("");
			newCellEding.setCellStyle(cellMergerStyle);
		}
		
		k = 2;
		row = sheet.getRow(k);
		//  动态添加指数列
		for(int i = 0 ; i < years2.size() ; i++) {
			HSSFCell newCel7 = row.createCell(flag);
			newCel7.setCellStyle(cellMergerStyle);
			newCel7.setCellValue(years2.get(i));
			flag++;
		}
		
		// 动态添加平均增长速度列
		for(int i = 0 ; i < years3.size() ; i++) {
			HSSFCell newCel8 = row.createCell(flag);
			newCel8.setCellStyle(cellMergerStyle);
			newCel8.setCellValue(years3.get(i));
			sheet.setColumnWidth(flag, 3000);
			flag++;
		}
		
		flag = 1;
		k = 3;
		// 添加数据(body)
		for(int i = 0 ; i < objList.size() ; i++) {
			Object obj = objList.get(i);
			row = sheet.createRow(k);
			// 向第一列添加数据
			HSSFCell newCell9 = row.createCell(0);
			newCell9.setCellStyle(cellStyle);
			newCell9.setCellValue("" + obj);
			for(int j = 0 ; j < years1.size() ; j++) {
				List<Object> objs = (List<Object>) map1.get(years1.get(j));
				Object object = objs.get(i);
				Method methodSl = object.getClass().getMethod("getSl");
				HSSFCell newCel10 = row.createCell(flag);
				newCel10.setCellStyle(cellStyle);
				newCel10.setCellValue("" + (Object)(methodSl.invoke(object)!=null?methodSl.invoke(object):""));// object的值
				flag ++;
			}
			
			for(int j = 0 ; j < years2.size() ; j++) {
				List<Object> objs = (List<Object>) map2.get(years2.get(j));
				Object object = objs.get(i);
				Method methodSl = object.getClass().getMethod("getSl");
				HSSFCell newCel11 = row.createCell(flag);
				newCel11.setCellStyle(cellStyle);
				newCel11.setCellValue("" + (Object)(methodSl.invoke(object)!=null?methodSl.invoke(object):""));// object的值
				flag ++;
			}
			
			for(int j = 0 ; j < years3.size() ; j++) {
				List<Object> objs = (List<Object>) map3.get(years3.get(j));
				Object object = objs.get(i);
				Method methodSl = object.getClass().getMethod("getSl");
				HSSFCell newCel12 = row.createCell(flag);
				newCel12.setCellStyle(cellStyle);
				newCel12.setCellValue("" + (Object)(methodSl.invoke(object)!=null?methodSl.invoke(object):""));// object的值
				flag ++;
			}
			
			k++;
			flag = 1;
		}
		os.flush();
		workbook.write(os);
	}
	
	// 培训班课程安排表
	public static void exportExcelByPxbkcapb(String templetPath, int startRow, int startColumn, Map<String, Object> map, String title, OutputStream os) throws Exception {
		// 读取模板文件
		FileInputStream in = new FileInputStream(new File(templetPath)); 
		HSSFWorkbook workbook = new HSSFWorkbook(in);
		// 获取第一个工作簿
		HSSFSheet sheet = workbook.getSheetAt(0);
		int k = startRow;
		HSSFRow row = null;
		// 标题头的样式
		HSSFCellStyle cellMergerStyle = workbook.createCellStyle();
		cellMergerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellMergerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellMergerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
		cellMergerStyle.setBottomBorderColor(HSSFColor.BLACK.index); 
		cellMergerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
		cellMergerStyle.setLeftBorderColor(HSSFColor.BLACK.index); 
		cellMergerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); 
		cellMergerStyle.setRightBorderColor(HSSFColor.BLACK.index); 
		cellMergerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); 
		cellMergerStyle.setTopBorderColor(HSSFColor.BLACK.index);
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 20);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		cellMergerStyle.setFont(font);
		// 数据的样式
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); 
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setLeftBorderColor(HSSFColor.BLACK.index); 
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setRightBorderColor(HSSFColor.BLACK.index); 
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); 
		cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
	
		// 添加头
		row = sheet.getRow(0);
		HSSFCell cell = row.getCell(0);
		cell.setCellValue(title);
		cell.setCellStyle(cellMergerStyle);
		
		Set<String> keys = map.keySet();
		Iterator<String> iter = keys.iterator();
		while(iter.hasNext()) {
			String key = iter.next();
			List<Object> objList = (List<Object>) map.get(key);
			if(objList.size() == 1) {
				// 添加数据(不合并)
				row = sheet.createRow(k);
				HSSFCell newCell = row.createCell(0);
				newCell.setCellValue(key);
				newCell.setCellStyle(cellStyle);
				
				newCell = row.createCell(1);
				Method methodSj = objList.get(0).getClass().getMethod("getYuliu1");
				newCell.setCellValue("" + methodSj.invoke(objList.get(0)));
				newCell.setCellStyle(cellStyle);
				
				newCell = row.createCell(2);
				Method methodKcmc = objList.get(0).getClass().getMethod("getKcmc");
				newCell.setCellValue("" + methodKcmc.invoke(objList.get(0)));
				newCell.setCellStyle(cellStyle);
				
				newCell = row.createCell(3);
				Method methodPxdd = objList.get(0).getClass().getMethod("getPxdd");
				newCell.setCellValue("" + methodPxdd.invoke(objList.get(0)));
				newCell.setCellStyle(cellStyle);
				
				newCell = row.createCell(4);
				Method methodZjr = objList.get(0).getClass().getMethod("getZjr");
				newCell.setCellValue("" + methodZjr.invoke(objList.get(0)));
				newCell.setCellStyle(cellStyle);
				
				k++;
			} else {
				// 添加数据(合并)
				row = sheet.createRow(k);
				HSSFCell newCell = row.createCell(0);
				newCell.setCellValue(key);
				sheet.addMergedRegion(new CellRangeAddress(k,objList.size()+k-1,0,0));
				newCell.setCellStyle(cellStyle);
				for(int j = 0 ; j < objList.size() ; j++) {
					Object object = objList.get(j);
					
					newCell = row.createCell(1);
					Method methodSj = object.getClass().getMethod("getYuliu1");
					newCell.setCellValue("" + methodSj.invoke(object));
					newCell.setCellStyle(cellStyle);
					
					newCell = row.createCell(2);
					Method methodKcmc = object.getClass().getMethod("getKcmc");
					newCell.setCellValue("" + methodKcmc.invoke(object));
					newCell.setCellStyle(cellStyle);
					
					newCell = row.createCell(3);
					Method methodPxdd = object.getClass().getMethod("getPxdd");
					newCell.setCellValue("" + methodPxdd.invoke(object));
					newCell.setCellStyle(cellStyle);
					
					newCell = row.createCell(4);
					Method methodZjr = object.getClass().getMethod("getZjr");
					newCell.setCellValue("" + methodZjr.invoke(object));
					newCell.setCellStyle(cellStyle);
					
					k++;
					row = sheet.createRow(k);
				}
			}
		}
		os.flush();
		workbook.write(os);
	}
	
	// 总库存
	public static void exportExcelByJzwz(String templetPath, List<?> dqList, List<?> objList, String[] fields, OutputStream os)
			throws Exception {
		// 读取模板文件
		FileInputStream in = new FileInputStream(new File(templetPath));
		HSSFWorkbook workbook = new HSSFWorkbook(in);
		// 获取第一个工作簿
		HSSFSheet sheet = workbook.getSheetAt(0);
		HSSFRow row = null;

		// 标题头的样式
		HSSFCellStyle cellMergerStyle = workbook.createCellStyle();
		cellMergerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellMergerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellMergerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellMergerStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		cellMergerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellMergerStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		cellMergerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellMergerStyle.setRightBorderColor(HSSFColor.BLACK.index);
		cellMergerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellMergerStyle.setTopBorderColor(HSSFColor.BLACK.index);
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 13);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		cellMergerStyle.setFont(font);

		// 数据的样式
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setTopBorderColor(HSSFColor.BLACK.index);

		// 添加头
		row = sheet.getRow(2);
		HSSFCell cell = row.createCell(1);
		cell.setCellValue("品种");
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, fields.length));
		cell.setCellStyle(cellMergerStyle);

		row = sheet.getRow(3);
		for(int i= 0 ; i < fields.length ; i++) {
			cell = row.createCell(i+1);
			cell.setCellValue(fields[i]);
			cell.setCellStyle(cellMergerStyle);
		}
		sheet.addMergedRegion(new CellRangeAddress(2, 3, fields.length+1, fields.length+1));
		row = sheet.getRow(2);
		cell = row.createCell(fields.length+1);
		cell.setCellValue("物资折价");
		cell.setCellStyle(cellMergerStyle);
		int k = 4;
		// 添加数据
		for(int i = 0 ; i < objList.size() ; i++) {
			row = sheet.createRow(k);
			cell = row.createCell(0);
			cell.setCellValue((String)dqList.get(i));
			cell.setCellStyle(cellStyle);
			String[] value = (String[]) objList.get(i);
			for(int j = 0 ; j < value.length ; j++) {
				cell = row.createCell(j+1);
				cell.setCellValue(value[j]);
				cell.setCellStyle(cellStyle);
			}
			k++;
		}
		os.flush();
		workbook.write(os);
	}
}